This tutorial explains how to use Magicodes.IE.Excel to complete the export of the Excel template for the textbook order form.
- This tutorial uses Magicodes.IE.Excel to complete the export of Excel templates
- Need to create Dto to complete the export
- Excel templates need to be prepared as required
In this tutorial, we will only demonstrate the use of Excel to complete the import of student data. We need to install the following packages in the prepared project with the following reference commands：
Install-Package Magicodes.IE.ExcelMagicodes.IE.Excel template export supports cell rendering and table rendering.
-
Cell rendering
Grammar.：
{{Company}} (School Name) Spring 2020 textbook order details {{Table>>BookInfos|RowNo}} //Table rendering starts {{Remark|>>Table}}//End of table rendering {{Image::ImageUrl?Width=50&Height=120&Alt=404}} //Image Rendering {{Image::ImageUrl?w=50&h=120&Alt=404}} //Image Rendering {{Image::ImageUrl?Alt=404}} //Image Rendering {{Image::ImgUrl?w=50&h=50&XOffset=100&YOffset=100}}Caution：
- Double curly brackets are required
- No support for expressions, etc.
- Support for subobject attributes
- Case-sensitive
-
Table Rendering
| RowNo | No | Name | Editor-in-Chief | Publisher | Price | Quantity of purchase | Remarks |
|---|---|---|---|---|---|---|---|
| {{Table>>BookInfos|RowNo}} | {{No}} | {{Name}} | {{EditorInChief}} | {{PublishingHouse}} | {{Price}} | {{PurchaseQuantity}} | {{Remark|>>Table}} |
The format of the table rendering is as described above:
-
The rendering syntax starts with "Table>>BookInfos|", where "BookInfos" is a list property
-
"RowNo", "No", etc. are all list fields
-
Must end with "|>>Table".
-
Does not support multiple Tables in a row (will support soon, please check the log for details)
Based on the above syntax, we can write the template as shown below.
The main code is shown below.
-
Textbook Ordering Information Dto
/// <summary> /// 教材订购信息 /// </summary> public class TextbookOrderInfo { /// <summary> /// 公司名称 /// </summary> public string Company { get; } /// <summary> /// 地址 /// </summary> public string Address { get; } /// <summary> /// 联系人 /// </summary> public string Contact { get; } /// <summary> /// 电话 /// </summary> public string Tel { get; } /// <summary> /// 制表人 /// </summary> public string Watchmaker { get; } /// <summary> /// 时间 /// </summary> public string Time { get; } /// <summary> /// 教材信息列表 /// </summary> public List<BookInfo> BookInfos { get; } public TextbookOrderInfo(string company, string address, string contact, string tel, string watchmaker, string time, List<BookInfo> bookInfo) { Company = company; Address = address; Contact = contact; Tel = tel; Watchmaker = watchmaker; Time = time; BookInfos = bookInfo; } }
As shown in the above code, the structure defined is basically the same as the template. One of the attributes BookInfos corresponds to a list, as shown in the definition below.
-
Textbook Information Dto:
/// <summary> /// 教材信息 /// </summary> public class BookInfo { /// <summary> /// 行号 /// </summary> public int RowNo { get; } /// <summary> /// 书号 /// </summary> public string No { get; } /// <summary> /// 书名 /// </summary> public string Name { get; } /// <summary> /// 主编 /// </summary> public string EditorInChief { get; } /// <summary> /// 出版社 /// </summary> public string PublishingHouse { get; } /// <summary> /// 定价 /// </summary> public string Price { get; } /// <summary> /// 采购数量 /// </summary> public int PurchaseQuantity { get; } /// <summary> /// 备注 /// </summary> public string Remark { get; } public BookInfo(int rowNo, string no, string name, string editorInChief, string publishingHouse, string price, int purchaseQuantity, string remark) { RowNo = rowNo; No = no; Name = name; EditorInChief = editorInChief; PublishingHouse = publishingHouse; Price = price; PurchaseQuantity = purchaseQuantity; Remark = remark; } }
Next, directly call the Magicodes.IE.Excel wrapped template export method, which is implemented from the interface IExportFileByTemplate：
/// <summary>
/// 根据模板导出文件
/// </summary>
public interface IExportFileByTemplate
{
/// <summary>
/// 根据模板导出
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fileName"></param>
/// <param name="data"></param>
/// <param name="template">HTML模板或模板路径</param>
/// <returns></returns>
Task<ExportFileInfo> ExportByTemplate<T>(string fileName, T data,
string template) where T : class;
}Through the above method of ExportByTemplate, we can complete the Excel template export. Specific use can be found in the following unit tests.
[Fact(DisplayName = "Sample Excel template for exporting textbook order details")]
public async Task ExportByTemplate_Test()
{
//Template Path
var tplPath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "ExportTemplates",
"Sample_Order_Form_for_Spring_2020_Teaching_Materials.xlsx");
//Creating Excel Export Objects
IExportFileByTemplate exporter = new ExcelExporter();
//Export Path
var filePath = Path.Combine(Directory.GetCurrentDirectory(), nameof(ExportByTemplate_Test) + ".xlsx");
if (File.Exists(filePath)) File.Delete(filePath);
//Export according to template
await exporter.ExportByTemplate(filePath,
new TextbookOrderInfo("Hunan Xinlai Information Technology Co.", "Hunan Changsha Yuelu District", "Snow Goose", "1367197xxxx", "Snow Goose", DateTime.Now.ToLongDateString(),
new List<BookInfo>()
{
new BookInfo(1, "0000000001", "《From Getting Started to Giving Up》", "Tom", "China Machine Press", "3.14", 100, "Remarks"),
new BookInfo(2, "0000000002", "《From Getting Started to Giving Up》", "Tom", "China Machine Press", "3.14", 100, "Remarks"),
new BookInfo(3, "0000000003", "《From Getting Started to Giving Up》", "Tom", "China Machine Press", "3.14", 100, "Remarks")
}),
tplPath);
}The results are shown in the following figure：
This is the end of the entire Excel template export tutorial, due to the hasty completion, there are many places to improve and optimize
The related library will be updated all the time, and there may be slight differences in functional experience with this tutorial, please refer to the relevant specific code, version logs, and unit test examples.